import matplotlib.pyplot as plt
import openpyxl
import numpy as np
from tqdm import tqdm
from openpyxl.styles import Border, Side, Alignment, Font, PatternFill


def best_fit(x, y):
    xbar = sum(x) / len(x)
    ybar = sum(y) / len(y)
    n = len(x)  # or len(y)

    numer = sum([xi * yi for xi, yi in zip(x, y)]) - n * xbar * ybar
    denum = sum([xi ** 2 for xi in x]) - n * xbar ** 2

    b = numer / denum
    a = ybar - b * xbar
    return a, b


def function1(x1: int):
    labels = ['2023/12', '2024/01', '2024/02', '2024/03']
    list1 = []
    for j in range(3, 7):
        temp = []
        for k in range(0, 4):
            temp.append(ws.cell(x1, k * 5 + j).value)
        list1.append(temp)

    x = np.arange(1, len(labels) + 1, 1)  # x轴刻度标签位置
    width = 0.1  # 柱子的宽度
    # 计算每个柱子在x轴上的位置，保证x轴刻度标签居中
    fig, ax1 = plt.subplots(sharey=True)
    ax1.bar(x - 1.5 * width, list1[0], width, label='产')
    ax1.bar(x - 0.5 * width, list1[1], width, label='销')
    ax1.bar(x + 0.5 * width, list1[2], width, label='其它入库')
    ax1.bar(x + 1.5 * width, list1[3], width, label='其它出库')

    plt.ylabel('数量（单位：pcs）')
    plt.title(ws.cell(x1, 1).value + ' 2023/12-2024/03产销存')
    # x轴刻度标签位置不进行计算
    plt.xticks(x, labels=labels)
    x = np.arange(0.5, 5, 1)
    y = []
    for j in range(0, 5):
        y.append(ws.cell(x1, j * 5 + 2).value)
    a, b = best_fit(x, y)
    ws.cell(i, 23).value = b
    ax1.bar(x, y, width, label='月末库存', color="c")

    plt.yticks([])
    for m, n in zip(x, y):
        plt.text(m, n, n, verticalalignment="center", horizontalalignment="center")
    fig.legend(loc='lower center', ncol=5, frameon=0)

    plt.rcParams['font.sans-serif'] = ['SimHei']
    plt.rcParams["axes.unicode_minus"] = False
    plt.savefig(
        fname="//192.168.70.101/19计划管理部/01.计划/5，物控管理/Data/2024.04.24成品产销存分析-柱状图版/pic/" + ws.cell(
            x1,
            1).value + ".png",
        # 图片名字
        dpi=600,  # 图片像素密度
    )
    plt.close()
    ws.cell(i,
            26).hyperlink = "//192.168.70.101/19计划管理部/01.计划/5，物控管理/Data/2024.04.24成品产销存分析-柱状图版/pic/" + ws.cell(
        i,
        1).value + ".png"
    ws.cell(i,
            25).hyperlink = "//192.168.70.101/19计划管理部/01.计划/5，物控管理/Data/2024.04.24成品产销存分析/pic/" + ws.cell(
        i,
        1).value + ".png"

    if ws.cell(i, 2).value < ws.cell(i, 7).value < ws.cell(i, 12).value < ws.cell(i, 17).value < ws.cell(i,
                                                                                                             22).value:
        ws.cell(i, 24).value = "是"
    else:
        ws.cell(i, 24).value = "否"
    font1 = Font(color="0000FF",underline="single")
    ws.cell(i,26).font =font1
    ws.cell(i, 25).font = font1


wb = openpyxl.load_workbook(
    r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\Data\2024.04.24成品产销存分析-柱状图版\数据汇总.xlsx")
ws = wb.worksheets[0]
for i in tqdm(range(2, ws.max_row + 1)):
    function1(i)
wb.save(r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\Data\2024.04.24成品产销存分析-柱状图版\数据汇总.xlsx")
